package com.hxgis.oc.util.ds;

import lombok.Getter;
import lombok.extern.slf4j.Slf4j;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;
import org.springframework.transaction.TransactionDefinition;
import org.springframework.transaction.TransactionStatus;
import org.springframework.transaction.support.DefaultTransactionDefinition;

import java.io.File;
import java.util.*;
import java.util.concurrent.locks.ReentrantReadWriteLock;

@Slf4j
public class FileDatabase {
    public static final String FieldID = "FID";
    public static final String FieldName = "FileName";


    @Getter
    private final String fileName;
    @Getter
    private final String filePath;
    private final Set<String> tables = new HashSet<>();
    private final static ReentrantReadWriteLock lock = new ReentrantReadWriteLock();

    private final JdbcTemplate jdbcTemplate;
    private final DataSourceTransactionManager dataSourceTransactionManager;

    public FileDatabase(String pFileName)
    {
        fileName = pFileName;
        filePath = new File(fileName).getParent();
        jdbcTemplate = SQLiteDataSource.createJdbcTemplate(fileName);
        dataSourceTransactionManager = new DataSourceTransactionManager(Objects.requireNonNull(jdbcTemplate.getDataSource()));
    }

    public boolean existTable(String table)
    {
        if(tables.contains(table)){
            return true;
        }

        List<Map<String, Object>> existTable = jdbcTemplate.queryForList(String.format("SELECT * FROM sqlite_master WHERE type = 'table' AND name = '%s'",table));
        return !existTable.isEmpty();
    }

    public void createTable(String table)
    {
        lock.writeLock().lock();
        try
        {
            if(existTable(table))
            {
                return;
            }
            jdbcTemplate.execute(String.format("CREATE TABLE %s (%s INTEGER NOT NULL PRIMARY KEY)",table,FieldID));
            DefaultTransactionDefinition def = new DefaultTransactionDefinition();
            def.setPropagationBehavior(TransactionDefinition.PROPAGATION_REQUIRED);
            TransactionStatus status = dataSourceTransactionManager.getTransaction(def);
            jdbcTemplate.execute(String.format("ALTER TABLE %s ADD COLUMN [%s] TEXT",table,FieldName));
            dataSourceTransactionManager.commit(status);
            tables.add(table);
        }
        catch(Exception e)
        {
            log.error(e.getMessage());
        }
        finally {
            lock.writeLock().unlock();
        }
    }

    public List<FileRecord> selectAll(String table) {
        return select(table,null,null);
    }

    public void insert(String table, long id, String fileName) {
        lock.writeLock().lock();
        try
        {
            String sql2 = String.format("INSERT INTO %s (%s,%s) VALUES(%d,'%s')",table,FieldID,FieldName,id,fileName);
            jdbcTemplate.execute(sql2);
        }
        catch(Exception e)
        {
            log.error(e.getMessage());
        }
        finally {
            lock.writeLock().unlock();
        }
    }

    public void insertIfNotExist(String table, long id, String fileName) {
        lock.writeLock().lock();
        try
        {
            String sql1 = String.format("SELECT %s FROM %s WHERE %s = %d",FieldID,table,FieldID,id);
            List<Map<String, Object>> existItem = jdbcTemplate.queryForList(sql1);
            if(existItem.isEmpty()){
                insert(table,id,fileName);
            }
        }
        catch(Exception e)
        {
            log.error(e.getMessage());
        }
        finally {
            lock.writeLock().unlock();
        }
    }

    public boolean exist(String table, long id) {
        if(!existTable(table))
        {
            return false;
        }

        String sql1 = String.format("SELECT %s FROM %s WHERE %s = %d",FieldID,table,FieldID,id);
        List<Map<String, Object>> existItem = jdbcTemplate.queryForList(sql1);
        return !existItem.isEmpty();
    }

    public List<FileRecord> select(String table, Long startTime, Long endTime) {
        lock.readLock().lock();
        List<FileRecord> list = new ArrayList<>();
        try {
            if(existTable(table)) {
                String sql1 = String.format("SELECT %s,%s FROM %s",FieldID,FieldName,table);
                if(startTime != null || endTime != null){
                    sql1 += " WHERE";
                    if(startTime != null){
                        sql1 += String.format(" %s > %d",FieldID,startTime);
                    }
                    if(endTime != null){
                        if(startTime != null){
                            sql1 += " &&";
                        }
                        sql1 += String.format(" %s < %d",FieldID,endTime);
                    }
                }
                sql1 += String.format(" ORDER BY %s",FieldID);

                List<Map<String, Object>> items = jdbcTemplate.queryForList(sql1);
                items.forEach((e->{
                    Object b1 = e.get(FieldID);
                    long id = b1.getClass() == Integer.class ? ((Integer) b1).longValue() : (long) b1;
                    String value = ((Map<?, ?>)e).get(FieldName).toString();
                    FileRecord record = new FileRecord();
                    record.setFID(id);
                    record.setFileName(value);
                    list.add(record);
                }));
            }
            return list;
        }
        catch(Exception e){
            log.error(e.getMessage());
        }
        finally {
            lock.readLock().unlock();
        }
        return list;
    }

    public int count(String table,Long startTime)
    {
        Integer integer = null;
        try{
            String sql1 = String.format("SELECT COUNT(*) FROM %s",table);

            if(startTime != null){
                sql1 += String.format(" WHERE %s >= %d",FieldID,startTime);
            }

            integer = jdbcTemplate.queryForObject(sql1, Integer.class);
        }catch(Exception e){
            log.error(e.getMessage());
        }
        return integer == null ? 0 : integer;
    }

    public File getFile(String table,String fileName) {
        return new File(filePath + File.separator + table + File.separator + fileName);
    }

    public File getFile(String table,long fileID) {
        String sql1 = String.format("SELECT %s FROM %s WHERE %s = %d",FieldName,table,FieldID,fileID);
        List<Map<String, Object>> items = jdbcTemplate.queryForList(sql1);
        if(!items.isEmpty()) {
            String fileName = items.get(0).get(FieldName).toString();
            return getFile(table,fileName);
        }

        return null;
    }

    public void delete(String table, Long fileID) {
        lock.writeLock().lock();
        String sql1 = String.format("SELECT %s FROM FROM %s WHERE %s = %d",FieldName,table,FieldID,fileID);
        try {
            List<Map<String, Object>> items = jdbcTemplate.queryForList(sql1);
            if(!items.isEmpty()) {
                String fileName = items.get(0).get(FieldName).toString();
                delete(table,fileID,fileName);
            }
        }catch(Exception e){
            log.error(e.getMessage());
        }finally {
            lock.writeLock().unlock();
        }
    }

    public void delete(String table, Long fileID, String fileName) {
        lock.writeLock().lock();

        String sql1 = String.format("DELETE FROM %s WHERE %s = %d",table,FieldID,fileID);
        try {
            jdbcTemplate.execute(sql1);
            File file = getFile(table,fileName);
            file.delete();
        }catch(Exception e){
            log.error(e.getMessage());
        }finally {
            lock.writeLock().unlock();
        }
    }

    public void deleteInvalid(String table, List<FileRecord> invalidIds) {
        lock.writeLock().lock();

        StringBuilder sb = new StringBuilder();
        for(int i=0;i<invalidIds.size();i++)
        {
            if(i > 0){
                sb.append(",");
            }
            sb.append(invalidIds.get(i).getFID());
        }

        String sql1 = String.format("DELETE FROM %s WHERE %s IN (%s)",table,FieldID,sb);

        try {
            jdbcTemplate.execute(sql1);
        }catch(Exception e){
            log.error(e.getMessage());
        }finally {
            lock.writeLock().unlock();
        }
    }

    public void deleteExpired(String dataCode, long clearTimeStamp) {
        lock.writeLock().lock();
        String sql1 = String.format("DELETE FROM %s WHERE %s < %d",dataCode,FieldID,clearTimeStamp);
        try {
            jdbcTemplate.execute(sql1);
        }catch(Exception e){
            log.error(e.getMessage());
        }finally {
            lock.writeLock().unlock();
        }
    }
}
